import java.io.PrintWriter;
import java.io.StringWriter;
import java.lang.reflect.InvocationTargetException;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

//import com.mysql.cj.jdbc.Driver;

import org.eclipse.swt.SWT;
import org.eclipse.swt.custom.SashForm;
import org.eclipse.swt.events.KeyAdapter;
import org.eclipse.swt.events.KeyEvent;
import org.eclipse.swt.events.SelectionAdapter;
import org.eclipse.swt.events.SelectionEvent;
import org.eclipse.swt.graphics.Image;
import org.eclipse.swt.layout.FormAttachment;
import org.eclipse.swt.layout.FormData;
import org.eclipse.swt.layout.FormLayout;
import org.eclipse.swt.layout.GridData;
import org.eclipse.swt.layout.GridLayout;
import org.eclipse.swt.widgets.Button;
import org.eclipse.swt.widgets.Control;
import org.eclipse.swt.widgets.Display;
import org.eclipse.swt.widgets.Event;
import org.eclipse.swt.widgets.Group;
import org.eclipse.swt.widgets.Label;
import org.eclipse.swt.widgets.Listener;
import org.eclipse.swt.widgets.MessageBox;
import org.eclipse.swt.widgets.Shell;
import org.eclipse.swt.widgets.Text;
import org.eclipse.swt.widgets.ToolBar;
import org.eclipse.swt.widgets.ToolItem;
import org.eclipse.swt.widgets.Tree;
import org.eclipse.swt.widgets.TreeItem;

public class DBMenu {
	Shell shell;
	Tree tree;
	Label description;

	ToolItem newDB;
	ToolItem newTable;
	ToolItem save;
	ToolItem load;
	ToolItem saveAs;
	ToolItem delSave;

	Connection connection;

	public DBMenu(Display display, Connection connection) throws IllegalAccessException, InstantiationException, ClassNotFoundException, SQLException, IllegalArgumentException, InvocationTargetException, NoSuchMethodException, SecurityException {
		Driver mysql = (Driver) Class.forName("com.mysql.cj.jdbc.Driver").getDeclaredConstructor().newInstance();
		Driver mssql = (Driver) Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").getDeclaredConstructor().newInstance();
		DriverManager.registerDriver(mysql);
		DriverManager.registerDriver(mssql);
		this.connection = connection;

		shell = new Shell(display, SWT.SHELL_TRIM);
		shell.setText("数据库");
		FormLayout layout = new FormLayout();
		layout.marginHeight = 5;
		layout.marginWidth = 5;
		shell.setLayout(layout);

		ToolBar bar = new ToolBar(shell, SWT.FLAT);
		FormData data = new FormData();
		data.left = new FormAttachment(0, 0);
		data.top = new FormAttachment(0, 0);
		data.right = new FormAttachment(100, 0);

		ToolItem connect = new ToolItem(bar, SWT.PUSH);
		connect.setImage(new Image(display, ClassLoader.getSystemResourceAsStream("connect.png")));
		connect.setToolTipText("连接");
		connect.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent e) {
				openConnQueryDialog();
			}
		});

		ToolItem sp1 = new ToolItem(bar, SWT.SEPARATOR);

		newDB = new ToolItem(bar, SWT.PUSH);
		newDB.setImage(new Image(display, ClassLoader.getSystemResourceAsStream("new_db.png")));
		newDB.setToolTipText("新建数据库");
		newDB.setEnabled(false);
		newDB.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent event) {
				String hints[] = {"数据库："};
				int vSpans[] = {1};
				openStringInputDialog("新建数据库", "", hints, vSpans, new Listener() {
					public void handleEvent(Event event) {
						if(!(event.data instanceof String[]))
							return;
						String inputs[] = (String[])event.data;
						if(inputs.length == 0)
							return;
						try {
							Statement st = DBMenu.this.connection.createStatement();
							st.execute("CREATE DATABASE " + inputs[0]);
							updateTree();
						} catch (SQLException e) {
							showExceptionDialog("新建数据库时发生错误。", e);
						}
					}
				});
			}
		});

		newTable = new ToolItem(bar, SWT.PUSH);
		newTable.setImage(new Image(display, ClassLoader.getSystemResourceAsStream("new_table.png")));
		newTable.setToolTipText("新建表格");
		newTable.setEnabled(false);
		newTable.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent e) {
				DBItem dbItem = getValidatedDBItem();
				TreeItem item = tree.getSelection()[0];
				if(dbItem == null)
					return;
				String hints[] = {"名称："};
				int vSpans[] = {4};
				String text = "新建表格于：\n"
							+ "数据库：" + dbItem.database;
				openStringInputDialog("新建表格", text, hints, vSpans, new Listener() {
					public void handleEvent(Event event) {
						if(!(event.data instanceof String[]))
							return;
						String inputs[] = (String[])event.data;
						if(inputs.length == 0)
							return;
						try {
							createNewTable(dbItem.database, inputs[0]);
							updateTreeItem(item);
						} catch(SQLException e) {
							showExceptionDialog("新建表格时出错。", e);
						}
					}
				});
			}
		});

//		ToolItem dropTable = new ToolItem(bar, SWT.PUSH);
//		dropTable.setImage(new Image(display, "drop_table.png"));
//		dropTable.setToolTipText("删除表格");

		ToolItem sp2 = new ToolItem(bar, SWT.SEPARATOR);

		save = new ToolItem(bar, SWT.PUSH);
		save.setImage(new Image(display, ClassLoader.getSystemResourceAsStream("save.png")));
		save.setToolTipText("保存");
		save.setEnabled(false);
		save.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent event) {
				DBItem dbItem = getValidatedDBItem();
				if(dbItem == null)
					return;
				try {
					save(dbItem.database, dbItem.table, dbItem.name);
				} catch(SQLException e) {
					showExceptionDialog("保存时发生错误。", e);
				}
			}
		});

		load = new ToolItem(bar, SWT.PUSH);
		load.setImage(new Image(display, ClassLoader.getSystemResourceAsStream("load.png")));
		load.setToolTipText("加载");
		load.setEnabled(false);
		load.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent event) {
				DBItem dbItem = getValidatedDBItem();
				if(dbItem == null)
					return;
				try {
					load(dbItem.database, dbItem.table, dbItem.name);
				} catch(SQLException e) {
					showExceptionDialog("加载存档时出现错误", e);
				}
			}
		});

		ToolItem sp3 = new ToolItem(bar, SWT.SEPARATOR);

		saveAs = new ToolItem(bar, SWT.PUSH);
		saveAs.setImage(new Image(display, ClassLoader.getSystemResourceAsStream("new_save.png")));
		saveAs.setToolTipText("新建存档");
		saveAs.setEnabled(false);
		saveAs.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent event) {
				DBItem dbItem = getValidatedDBItem();
				if(dbItem == null)
					return;
				TreeItem item = tree.getSelection()[0];

				String text = "新建存档于：\n"
							+ "数据库：" + dbItem.database + '\n'
							+ "表格：" + dbItem.table;
				String hints[] = {"名称：", "描述："};
				int vSpans[] = {1, 4};
				openStringInputDialog("新建存档", text, hints, vSpans, new Listener() {
					public void handleEvent(Event event) {
						if(!(event.data instanceof String[])) {
							System.out.println("Data not instance of String[]");
							return;
						}
						String inputs[] = (String[])event.data;
						if(inputs.length < 2) {
							System.out.println("Input length < 2");
							return;
						}
						try {
							newSave(dbItem.database, dbItem.table, inputs[0], inputs[1]);
							updateTreeItem(item);
						} catch(SQLException e) {
							showExceptionDialog("新建存档时发生错误。", e);
						}
					}
				});
			}
		});

		delSave = new ToolItem(bar, SWT.PUSH);
		delSave.setImage(new Image(display, ClassLoader.getSystemResourceAsStream("delete_save.png")));
		delSave.setToolTipText("删除存档");
		delSave.setEnabled(false);
		delSave.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent event) {
				DBItem dbItem = getValidatedDBItem();
				if(dbItem == null)
					return;
				TreeItem parent = tree.getSelection()[0].getParentItem();
				try {
					delSave(dbItem.database, dbItem.table, dbItem.name);
					updateTreeItem(parent);
				} catch(SQLException e) {
					showExceptionDialog("删除存档时发生错误。", e);
				}
			}
		});

		bar.pack();

		SashForm sf1 = new SashForm(shell, SWT.HORIZONTAL | SWT.SMOOTH);
		data = new FormData();
		data.left = new FormAttachment(0, 0);
		data.right = new FormAttachment(100, 0);
		data.top = new FormAttachment(bar);
		data.bottom = new FormAttachment(100, 0);
		sf1.setLayoutData(data);

		Group gpTree = new Group(sf1, SWT.SHADOW_IN);
		gpTree.setLayout(new FormLayout());
		gpTree.setText("内容");
		tree = new Tree(gpTree, SWT.V_SCROLL);
		tree.setBackground(shell.getBackground());
		setFill(tree);
		tree.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent event) {
				if(!(event.item instanceof TreeItem))
					return;
				TreeItem item = (TreeItem)event.item;
				if(!(validateTreeItem(item))) {
					setDatabaseToolItemsEnabled(false);
					setTableToolItemsEnabled(false);
					setRowToolItemsEnabled(false);
					return;
				}
				final DBItem.Type DATABASE = DBItem.Type.DATABASE, 
								  TABLE = DBItem.Type.TABLE, 
								  ROW = DBItem.Type.ROW;
				DBItem dbItem = (DBItem)item.getData();
				DBItem.Type type = dbItem.type;

				String text = "数据库：" + dbItem.database + '\n';
				if(type == TABLE || type == ROW) {
					text += "表格：" + dbItem.table + '\n';
					setDatabaseToolItemsEnabled(false);
				} else {
					setDatabaseToolItemsEnabled(true);
					setTableToolItemsEnabled(false);
					setRowToolItemsEnabled(false);
				}
				if(type == TABLE) {
					setRowToolItemsEnabled(false);
					if(!dbItem.tableSupported) {
						text += "该表格不包含存档、读档所需的列，或列的数据类型不满足要求。";
						setTableToolItemsEnabled(false);
					} else
						setTableToolItemsEnabled(true);
				}else if(type == ROW) {
					text += "名称：" + dbItem.name + "\n\n";
					try {
						Statement st = DBMenu.this.connection.createStatement();
						st.execute("USE " + dbItem.database);
						ResultSet rs = st.executeQuery("SELECT Description FROM " + dbItem.table + " WHERE Name='" + dbItem.name + "';");
						rs.next();
						text += rs.getString(1);
					} catch(Exception e) {
						e.printStackTrace();
					}

					setTableToolItemsEnabled(false);
					setRowToolItemsEnabled(true);
				}
				description.setText(text);
			}
		});

		Group gpDescription = new Group(sf1, SWT.SHADOW_OUT);
		gpDescription.setText("描述");
		gpDescription.setLayout(new FormLayout());
		description = new Label(gpDescription, SWT.WRAP);
		setFill(description);

		shell.addKeyListener(new KeyAdapter() {
			public void keyPressed(KeyEvent e) {
				System.out.println(e.keyCode+", F5: "+SWT.F5);
				if(e.keyCode == SWT.F5) {
					updateTree();
				}
			}
		});

		shell.pack();
		shell.open();

		if(this.connection == null) {
			openConnQueryDialog();
		}
	}

	void newSave(String database, String table, String name, String description) throws SQLException {
		Game game = Main.singleton.getGame();
		int mapWidth = game.getMapWidth();
		int mapHeight = game.getMapHeight();
		boolean periodicBorder = game.getPeriodicBorder();
		boolean cells[][] = game.getCells();
		byte raw[] = new byte[(mapWidth+4)*(mapHeight+4)];
		for(int i = 0; i < raw.length; i++) {
			raw[i] = (byte) (cells[i / (mapWidth+4)][i % (mapWidth+4)] ? 1 : 0);
		}

		connection.createStatement().execute("USE " + database);
		String sql = "INSERT INTO " + table + " (Name, Description, mapWidth, mapHeight, periodicBorder, cells) VALUES (?, ?, ?, ?, ?, ?);";
		PreparedStatement st = connection.prepareStatement(sql);
		st.setString(1, name);
		st.setString(2, description);
		st.setInt(3, mapWidth);
		st.setInt(4, mapHeight);
		st.setBoolean(5, periodicBorder);
		Blob blob = connection.createBlob();
		blob.setBytes(1, raw);
		st.setBlob(6, blob);
		st.execute();
	}

	void save(String database, String table, String name) throws SQLException {
		Game game = Main.singleton.getGame();
		int mapWidth = game.getMapWidth();
		int mapHeight = game.getMapHeight();
		boolean periodicBorder = game.getPeriodicBorder();
		boolean cells[][] = game.getCells();
		byte raw[] = new byte[(mapWidth+4)*(mapHeight+4)];
		for(int i = 0; i < raw.length; i++) {
			raw[i] = (byte) (cells[i / (mapWidth+4)][i % (mapWidth+4)] ? 1 : 0);
		}

		connection.createStatement().execute("USE " + database);
		String sql = "UPDATE " + table + "\n"
				   + "SET mapWidth=?, mapHeight=?, periodicBorder=?, cells=?\n"
				   + "WHERE Name='" + name + "';";
		PreparedStatement st = connection.prepareStatement(sql);
		st.setInt(1, mapWidth);
		st.setInt(2, mapHeight);
		st.setBoolean(3, periodicBorder);
		Blob blob = connection.createBlob();
		blob.setBytes(1, raw);
		st.setBlob(4, blob);
		st.execute();
	}

	void delSave(String database, String table, String name) throws SQLException {
			Statement st = connection.createStatement();
			st.execute("USE " + database);
			st.execute("DELETE FROM " + table + " WHERE Name='" + name + "';");
	}

	void load(String database, String table, String row) throws SQLException {
		Statement st = connection.createStatement();
		st.execute("USE " + database + ';');
		String sql = "SELECT mapWidth, mapHeight, periodicBorder, cells "
				   + "FROM " + table + ' '
				   + "WHERE Name='" + row + "';";
		ResultSet rs = st.executeQuery(sql);
		rs.next();

		int mapWidth = rs.getInt("mapWidth");
		int mapHeight = rs.getInt("mapHeight");
		boolean periodicBorder = rs.getBoolean("periodicBorder");
		byte raw[] = rs.getBytes("cells");
		try {
			Main.singleton.getGame().load(mapWidth, mapHeight, periodicBorder, raw);
		} catch(Exception e) {
			showExceptionDialog("加载存档时发生错误。", e);
		}
	}

	void setFill(Control control) {
		FormData data = new FormData();
		data.left = new FormAttachment(0, 0);
		data.top = new FormAttachment(0, 0);
		data.right = new FormAttachment(100, 0);
		data.bottom = new FormAttachment(100, 0);
		control.setLayoutData(data);
	}

	void openConnQueryDialog() {
		Shell dialog = new Shell(shell.getDisplay(), SWT.SHELL_TRIM);
		dialog.setLayout(new GridLayout(2, false));

		Label lbHostname = new Label(dialog, SWT.NONE);
		lbHostname.setText("域名：");
		Text txtHostname = new Text(dialog, SWT.BORDER);
		txtHostname.setText("localhost");
		txtHostname.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, false, false));

		Label lbUsername = new Label(dialog, SWT.NONE);
		lbUsername.setText("用户名：");
		Text txtUsername = new Text(dialog, SWT.BORDER);
		txtUsername.setText("game");
		txtUsername.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, false, false));

		Label lbPassword = new Label(dialog, SWT.NONE);
		lbPassword.setText("密码：");
		Text txtPassword = new Text(dialog, SWT.BORDER);
		txtPassword.setEchoChar('*');
		txtPassword.setText("Conway123");
		txtPassword.setLayoutData(new GridData(SWT.FILL, SWT.CENTER, false, false));

		Button btConnect = new Button(dialog, SWT.PUSH);
		btConnect.setText("连接");
		btConnect.setLayoutData(new GridData(SWT.CENTER, SWT.CENTER, true, false, 2, 1));
		btConnect.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent event) {
				String url = "jdbc:mysql://"+txtHostname.getText()+"/game";
				try {
					Connection newConnection = DriverManager.getConnection(url, txtUsername.getText(), txtPassword.getText());
					if(connection != null && !connection.isClosed())
						connection.close();
					connection = newConnection;
					updateTree();
					newDB.setEnabled(true);
				} catch (SQLException e) {
					showExceptionDialog(dialog, "URL: "+url, e);
					return;
				}
				dialog.dispose();
			}
		});

		dialog.pack();
		dialog.open();

		return;
	}

	void setDatabaseToolItemsEnabled(boolean enabled) {
		newTable.setEnabled(enabled);
	}

	void setTableToolItemsEnabled(boolean enabled) {
		saveAs.setEnabled(enabled);
	}

	void setRowToolItemsEnabled(boolean enabled) {
		save.setEnabled(enabled);
		load.setEnabled(enabled);
		delSave.setEnabled(enabled);
	}

	void openStringInputDialog(String title, String text, String hints[], int vSpans[], Listener listener) {
		Shell dialog = new Shell(shell, SWT.TITLE | SWT.CLOSE);
		dialog.setText(title);
		dialog.setLayout(new GridLayout(2, false));

		Label lbMsg = new Label(dialog, SWT.NONE);
		lbMsg.setText(text);
		GridData data = new GridData();
		data.horizontalSpan = 2;
		lbMsg.setLayoutData(data); 

		Text inputs[] = new Text[Math.min(hints.length, vSpans.length)];
		for(int i = 0; i < inputs.length; i++) {
			data = new GridData();
			data.verticalSpan = vSpans[i];
			data.horizontalAlignment = SWT.TRAIL;
			Label lbHint = new Label(dialog, SWT.NONE);
			lbHint.setText(hints[i]);
			lbHint.setLayoutData(data);
			inputs[i] = new Text(dialog, SWT.BORDER);
			data.horizontalAlignment = SWT.FILL;
			inputs[i].setLayoutData(data);
		}

		Button btConfirm = new Button(dialog, SWT.PUSH);
		btConfirm.setText("确认");
		btConfirm.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent e) {
				Event event = new Event();
				String result[] = new String[inputs.length];
				for(int i = 0; i < result.length; i++) {
					result[i] = new String(inputs[i].getText());
				}
				event.data = result;
				dialog.notifyListeners(SWT.SetData, event);
				dialog.dispose();
			}
		});
		data = new GridData();
		data.horizontalAlignment = SWT.CENTER;
		btConfirm.setLayoutData(data);

		Button btCancel = new Button(dialog, SWT.PUSH);
		btCancel.setText("取消");
		btCancel.addSelectionListener(new SelectionAdapter() {
			public void widgetSelected(SelectionEvent e) {
				dialog.dispose();
			}
		});
		btCancel.setLayoutData(data);

		dialog.addListener(SWT.SetData, listener);
		dialog.pack();
		dialog.open();
	}

	void updateTree() {
		if(connection != null) {
			try {
				if(connection.isClosed()) {
					throw new Exception("连接已关闭。\n"+connection.toString());
				}
				tree.removeAll();
				Statement st = connection.createStatement();
				ResultSet rs = st.executeQuery("SHOW DATABASES;");
				while(rs.next()) {
					String name = rs.getString(1);
					TreeItem item = new TreeItem(tree, SWT.NONE);
					item.setText(name);
					DBItem db = new DBItem(DBItem.Type.DATABASE);
					db.database = name;
					item.setData(db);

					updateTreeItem(item);
				}
			} catch (Exception e) {
				showExceptionDialog("更新树状图时发生错误。", e);
			}
			
		} else {
			System.out.println("Connecion is null");
		}
	}

	/**
	 * Validates specified table to see if whether the game can save to and load from it.
	 * A table supported by the game must contain following columns: <ul>
	 * <li>Name, 			with datatype of CHAR, VARCHAR, LONGVARCHAR, NCHAR, NVARCHAR or LONGNVARCHAR, not null, primary key</li>
	 * <li>Description, 	with datatype of CHAR, VARCHAR, LONGVARCHAR, NCHAR, NVARCHAR or LONGNVARCHAR</li>
	 * <li>mapWidth, 		with datatype of INTEGER, not null</li>
	 * <li>mapHeight, 		with datatype of INTEGER, not null</li>
	 * <li>periodicBorder, 	with datatype of BIT or BOOLEAN, not null</li>
	 * <li>cells, 			with datatype of BINARY, VARBINARY, or LONGVARBINARY, not null</li>
	 * </ul>
	 * <p>
	 * <b>NOTE: </b>Datatype detection is done via {@link java.sql.ResultSetMetaData#getColumnType(int)}
	 * </p>
	 * 
	 * @param database Name of the database containing the table. 
	 * @param table Name of the table.
	 * @return Whether the game can save to and load from the specified table, 
	 * or false if any exception occurs.
	 */
	boolean validateTable(String database, String table) {
		try {
			Statement st = connection.createStatement();
			st.execute("USE "+database+';');
			ResultSet rs = st.executeQuery("SELECT * FROM "+table);
			ResultSetMetaData md = rs.getMetaData();
			ResultSet primaryKeys = connection.getMetaData().getPrimaryKeys(null, database, table);

			final String COLUMNS[] = {"Name", "Description", "mapWidth", "mapHeight", "periodicBorder", "cells"};
			final int TYPES[][] = new int[6][];
			TYPES[0] = new int[6];
			TYPES[0][0] = Types.CHAR; TYPES[0][1] = Types.VARCHAR; TYPES[0][2] = Types.LONGVARCHAR; TYPES[0][3] = Types.NCHAR; TYPES[0][4] = Types.NVARCHAR; TYPES[0][5] = Types.LONGNVARCHAR;
			TYPES[1] = TYPES[0];
			TYPES[2] = new int[1];
			TYPES[2][0] = Types.INTEGER;
			TYPES[3] = TYPES[2];
			TYPES[4] = new int[2];
			TYPES[4][0] = Types.BIT;
			TYPES[4][1] = Types.BOOLEAN;
			TYPES[5] = new int[3];
			TYPES[5][0] = Types.BINARY;
			TYPES[5][1] = Types.VARBINARY;
			TYPES[5][2] = Types.LONGVARBINARY;
			final boolean NULLABLE[] = {false, true, false, false, false, false};

			boolean flag = false;
			while(primaryKeys.next()) {
				if(primaryKeys.getString(4).toLowerCase().equals(COLUMNS[0].toLowerCase())) {
					flag = true;
					break;
				}
			}
			if(!flag) {
//				System.out.println("No primary key found. ");
//				System.out.println(primaryKeys.toString());
				return false;
			}

			for(int i = 0; i < COLUMNS.length; i++) {
				int index;
				try {
					index = rs.findColumn(COLUMNS[i]);
				} catch(SQLException e) {
					/*
					System.out.println("Exception when trying to find column: "+e.getMessage());
					System.out.println("	Database: "+database);
					System.out.println("	Table: "+table);
					System.out.println("	Column: "+COLUMNS[i]);
					*/
					return false;
				}
				int type = md.getColumnType(index);
				int nullability = md.isNullable(index);

				flag = false;
				if(!NULLABLE[i] ? nullability!=DatabaseMetaData.columnNoNulls : false)
					flag = true;
				if(!flag) {
					flag = true;
					for(int j = 0; j < TYPES[i].length; j++)
						if(TYPES[i][j] == type) {
							flag = false;
							break;
						}
				}
				if(flag) {
//					/*
					System.out.println("Datatype or nullability mismatch. ");
					System.out.println("	Database: "+database);
					System.out.println("	Table: "+table);
					System.out.println("	Column: "+COLUMNS[i]);
					System.out.println("	Datatype: "+type);
					System.out.print("	Desired datatype: ");
					for(int t : TYPES[i]) {
						System.out.print(t+" ");
					}
					System.out.print('\n');
					System.out.println("	Nullability: "+nullability);
					System.out.println("	Must be nonull: "+!NULLABLE[i]);
//					*/
					return false;
				}
			}
			return true;
		} catch (Exception e) {
			System.out.println("Exception occured. " + e.toString());
			return false;
		}
	}

	boolean validateTreeItem(TreeItem item) {
		if(!(item.getData() instanceof DBItem))
			return false;

		DBItem dbItem = (DBItem)item.getData();
		if(!dbItem.validate())
			return false;
		return true;
	}

	void updateTreeItem(TreeItem item) {
		item.removeAll();
		if(!validateTreeItem(item))
			return;
		DBItem dbItem = (DBItem)item.getData();
		// final DBItem.Type DATABASE = DBItem.Type.DATABASE, 
		final DBItem.Type TABLE = DBItem.Type.TABLE, 
						  ROW = DBItem.Type.ROW;
		// System.out.println("dbItem: " + dbItem.toString());
		if(dbItem.type == ROW)
			return;
		if(dbItem.type == TABLE)
			if(!validateTable(dbItem.database, dbItem.table)) {
				return;
			}
		dbItem.tableSupported = true;

		try {
			if(connection.isClosed()) {
				throw new Exception("连接已关闭。\n"+connection.toString());
			}

			Statement stmt = connection.createStatement();
			ResultSet rs;
			stmt.execute("USE " + dbItem.database + ';');
			String query = dbItem.type==TABLE ? 
						   "SELECT * FROM " + dbItem.table + ';' :
						   "SHOW TABLES;";
			rs = stmt.executeQuery(query);

			while(rs.next()) {
				String name = rs.getString(1);
				// System.out.println("Name: "+name);
				TreeItem subItem = new TreeItem(item, SWT.NONE);
				subItem.setText(name);
				DBItem dbSubItem = dbItem.clone();
				if(dbItem.type == TABLE) {
					dbSubItem.type = ROW;
					dbSubItem.name = name;
				} else {
					dbSubItem.type = TABLE;
					dbSubItem.table = name;
				}
				subItem.setData(dbSubItem);

				if(dbSubItem.type != ROW)
					updateTreeItem(subItem);
			}
		} catch(Exception e) {
			showExceptionDialog("更新树状图项目时发生错误。", e);
		}
	}

	DBItem getValidatedDBItem() {
		if(tree.getSelectionCount() != 1)
			return null;
		TreeItem item = tree.getSelection()[0];
		if(!validateTreeItem(item))
			return null;
		return (DBItem)item.getData();
	}

	/**
	 * Create new table in specified database. The game can save to and load from the table. 
	 * The new table has following columns: <ul>
	 * <li>Name, 			with datatype of NVARCHAR(64) , not null, primary key</li>
	 * <li>Description, 	with datatype of NVARCHAR(2048)</li>
	 * <li>mapWidth, 		with datatype of INTEGER, not null</li>
	 * <li>mapHeight, 		with datatype of INTEGER, not null</li>
	 * <li>periodicBorder, 	with datatype of BOOLEAN, not null</li>
	 * <li>cells, 			with datatype of BLOB, not null</li>
	 * </ul>
	 * @param database Name of the database of the new table. 
	 * @param table Name of the new table. 
	 * @throws SQLException If a database access error occurs, or the connection is closed. 
	 */
	void createNewTable(String database, String table) throws SQLException {
		Statement st = connection.createStatement();
		st.execute("USE "+database+';');
		final String SQL = "CREATE TABLE "+table+"("
				   + "Name NVARCHAR(64) NOT NULL, "
				   + "Description NVARCHAR(2048), "
				   + "mapWidth INTEGER NOT NULL, "
				   + "mapHeight INTEGER NOT NULL, "
				   + "periodicBorder BOOLEAN NOT NULL, "
				   + "cells BLOB NOT NULL, "
				   + "PRIMARY KEY (Name)"
				   + ");";
		st.execute(SQL);
	}

	void showExceptionDialog(String hint, Exception e) {
		showExceptionDialog(shell, hint, e);
	}

	void showExceptionDialog(Shell parent, String hint, Exception e) {
		MessageBox box = new MessageBox(parent, SWT.ICON_ERROR);
		box.setText("错误");
		String msg = "";
		if(hint != null)
			msg = hint;
		msg += '\n';
		StringWriter strWriter = new StringWriter();
		PrintWriter writer = new PrintWriter(strWriter);
		e.printStackTrace(writer);
		msg += strWriter.toString();
		box.setMessage(msg);
		box.open();
	}
}
